

package com.junjie.shortlink.project.dao.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import com.baomidou.mybatisplus.core.metadata.IPage;
import com.junjie.shortlink.project.dao.entity.LinkAccessLogsDO;
import com.junjie.shortlink.project.dao.entity.LinkAccessStatsDO;
import com.junjie.shortlink.project.dto.req.ShortLinkGroupStatsAccessRecordReqDTO;
import com.junjie.shortlink.project.dto.req.ShortLinkStatsReqDTO;
import com.junjie.shortlink.project.dto.resp.ShortLinkGroupStatsReqDTO;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 访问日志监控持久层
 */
public interface LinkAccessLogsMapper extends BaseMapper<LinkAccessLogsDO> {
    /**
     * 根据短链接获取指定日期内高频访问IP数据
     */
    @Select("SELECT " +
            "    tlal.ip, " +
            "    COUNT(tlal.ip) AS count " +
            "FROM " +
            "    t_link tl INNER JOIN " +
            "    t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "    tlal.full_short_url = #{param.fullShortUrl} " +
            "    AND tl.gid = #{param.gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = #{param.enableStatus} " +
            "    AND tlal.create_time BETWEEN #{param.startDate} and #{param.endDate} " +
            "GROUP BY " +
            "    tlal.full_short_url, tl.gid, tlal.ip " +
            "ORDER BY " +
            "    count DESC " +
            "LIMIT 5;")
    List<HashMap<String, Object>> listTopIpByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

    /**
     * 根据分组获取指定日期内高频访问IP数据
     */
    @Select("SELECT " +
            "    tlal.ip, " +
            "    COUNT(tlal.ip) AS count " +
            "FROM " +
            "    t_link tl INNER JOIN " +
            "    t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "    tl.gid = #{param.gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = '0' " +
            "    AND tlal.create_time BETWEEN #{param.startDate} and #{param.endDate} " +
            "GROUP BY " +
            "    tl.gid, tlal.ip " +
            "ORDER BY " +
            "    count DESC " +
            "LIMIT 5;")
    List<HashMap<String, Object>> listTopIpByGroup(@Param("param") ShortLinkGroupStatsReqDTO requestParam);

    /**
     * 根据短链接获取指定日期内新旧访客数据
     */
    @Select("SELECT " +
            "    SUM(old_user) AS oldUserCnt, " +
            "    SUM(new_user) AS newUserCnt " +
            "FROM ( " +
            "    SELECT " +
            "        CASE WHEN COUNT(DISTINCT DATE(tlal.create_time)) > 1 THEN 1 ELSE 0 END AS old_user, " +
            "        CASE WHEN COUNT(DISTINCT DATE(tlal.create_time)) = 1 AND MAX(tlal.create_time) >= #{param.startDate} AND MAX(tlal.create_time) <= #{param.endDate} THEN 1 ELSE 0 END AS new_user " +
            "    FROM " +
            "        t_link tl INNER JOIN " +
            "        t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "    WHERE " +
            "        tlal.full_short_url = #{param.fullShortUrl} " +
            "        AND tl.gid = #{param.gid} " +
            "        AND tl.enable_status = #{param.enableStatus} " +
            "        AND tl.del_flag = '0' " +
            "    GROUP BY " +
            "        tlal.user " +
            ") AS user_counts;")
    HashMap<String, Object> findUvTypeCntByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

    /**
     * 获取用户信息是否新老访客
     */
    @Select("<script> " +
            "SELECT " +
            "    tlal.user, " +
            "    CASE " +
            "        WHEN MIN(tlal.create_time) BETWEEN #{startDate} AND #{endDate} THEN '新访客' " +
            "        ELSE '老访客' " +
            "    END AS uvType " +
            "FROM " +
            "    t_link tl INNER JOIN " +
            "    t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "    tlal.full_short_url = #{fullShortUrl} " +
            "    AND tl.gid = #{gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = #{enableStatus} " +
            "    AND tlal.user IN " +
            "    <foreach item='item' index='index' collection='userAccessLogsList' open='(' separator=',' close=')'> " +
            "        #{item} " +
            "    </foreach> " +
            "GROUP BY " +
            "    tlal.user;" +
            "</script>")
    List<Map<String, Object>> selectUvTypeByUsers(
            @Param("gid") String gid,
            @Param("fullShortUrl") String fullShortUrl,
            @Param("enableStatus") Integer enableStatus,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("userAccessLogsList") List<String> userAccessLogsList
    );

    /**
     * 获取分组用户信息是否新老访客
     */
    @Select("<script> " +
            "SELECT " +
            "    tlal.user, " +
            "    CASE " +
            "        WHEN MIN(tlal.create_time) BETWEEN #{startDate} AND #{endDate} THEN '新访客' " +
            "        ELSE '老访客' " +
            "    END AS uvType " +
            "FROM " +
            "    t_link tl INNER JOIN " +
            "    t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "    tl.gid = #{gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = '0' " +
            "    AND tlal.user IN " +
            "    <foreach item='item' index='index' collection='userAccessLogsList' open='(' separator=',' close=')'> " +
            "        #{item} " +
            "    </foreach> " +
            "GROUP BY " +
            "    tlal.user;" +
            "</script>")
    List<Map<String, Object>> selectGroupUvTypeByUsers(
            @Param("gid") String gid,
            @Param("startDate") String startDate,
            @Param("endDate") String endDate,
            @Param("userAccessLogsList") List<String> userAccessLogsList
    );

    /**
     * 根据短链接获取指定日期内PV、UV、UIP数据
     */
    @Select("SELECT " +
            "    COUNT(tlal.user) AS pv, " +
            "    COUNT(DISTINCT tlal.user) AS uv, " +
            "    COUNT(DISTINCT tlal.ip) AS uip " +
            "FROM " +
            "    t_link tl INNER JOIN " +
            "    t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "    tlal.full_short_url = #{param.fullShortUrl} " +
            "    AND tl.gid = #{param.gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = #{param.enableStatus} " +
            "    AND tlal.create_time BETWEEN #{param.startDate} and #{param.endDate} " +
            "GROUP BY " +
            "    tlal.full_short_url, tl.gid;")
    LinkAccessStatsDO findPvUvUidStatsByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);

    /**
     * 根据分组获取指定日期内PV、UV、UIP数据
     */
    @Select("SELECT " +
            "    COUNT(tlal.user) AS pv, " +
            "    COUNT(DISTINCT tlal.user) AS uv, " +
            "    COUNT(DISTINCT tlal.ip) AS uip " +
            "FROM " +
            "    t_link tl INNER JOIN " +
            "    t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "    tl.gid = #{param.gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = '0' " +
            "    AND tlal.create_time BETWEEN #{param.startDate} and #{param.endDate} " +
            "GROUP BY " +
            "    tl.gid;")
    LinkAccessStatsDO findPvUvUidStatsByGroup(@Param("param") ShortLinkGroupStatsReqDTO requestParam);

    @Select("SELECT " +
            "    tlal.* " +
            "FROM " +
            "    t_link tl " +
            "    INNER JOIN t_link_access_logs tlal ON tl.full_short_url = tlal.full_short_url " +
            "WHERE " +
            "    tl.gid = #{param.gid} " +
            "    AND tl.del_flag = '0' " +
            "    AND tl.enable_status = '0' " +
            "    AND tlal.create_time BETWEEN #{param.startDate} and #{param.endDate} " +
            "ORDER BY " +
            "    tlal.create_time DESC")
    IPage<LinkAccessLogsDO> selectGroupPage(@Param("param") ShortLinkGroupStatsAccessRecordReqDTO requestParam);


//    /**
//     * 根据短链接获取指定日期内PV、UV、UIP数据
//     * uv和uip加个distinct就可以统计了
//     */
//    @Select("SELECT " +
//            "    COUNT(user) AS pv, " +
//            "    COUNT(DISTINCT user) AS uv, " +
//            "    COUNT(DISTINCT ip) AS uip " +
//            "FROM " +
//            "    t_link_access_logs " +
//            "WHERE " +
//            "    full_short_url = #{param.fullShortUrl} " +
//            "    AND gid = #{param.gid} " +
//            "    AND create_time BETWEEN #{param.startDate} and #{param.endDate} " +
//            "GROUP BY " +
//            "    full_short_url, gid;")
//    LinkAccessStatsDO findPvUvUidStatsByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);
//
//    /**
//     * 根据短链接获取指定日期内新旧访客数据
//     */
//    @Select("SELECT " +
//            "    SUM(old_user) AS oldUserCnt, " +
//            "    SUM(new_user) AS newUserCnt " +
//            "FROM ( " +
//            "    SELECT " +
//            "        CASE WHEN COUNT(DISTINCT DATE(create_time)) > 1 THEN 1 ELSE 0 END AS old_user, " +
//            "        CASE WHEN COUNT(DISTINCT DATE(create_time)) = 1 AND MAX(create_time) >= #{param.startDate} AND MAX(create_time) <= #{param.endDate} THEN 1 ELSE 0 END AS new_user " +
//            "    FROM " +
//            "        t_link_access_logs " +
//            "    WHERE " +
//            "        full_short_url = #{param.fullShortUrl} " +
//            "        AND gid = #{param.gid} " +
//            "    GROUP BY " +
//            "        user " +
//            ") AS user_counts;")
//    HashMap<String, Object> findUvTypeCntByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);
//
//    /**
//     * 根据短链接获取指定日期内高频访问IP数据
//     */
//    @Select("SELECT " +
//            "    ip, " +
//            "    COUNT(ip) AS count " +
//            "FROM " +
//            "    t_link_access_logs " +
//            "WHERE " +
//            "    full_short_url = #{param.fullShortUrl} " +
//            "    AND gid = #{param.gid} " +
//            "    AND create_time BETWEEN #{param.startDate} and #{param.endDate} " +
//            "GROUP BY " +
//            "    full_short_url, gid, ip " +
//            "ORDER BY " +
//            "    count DESC " +
//            "LIMIT 5;")
//    List<HashMap<String, Object>> listTopIpByShortLink(@Param("param") ShortLinkStatsReqDTO requestParam);
//
//
//    /**
//     * 获取用户信息是否新老访客
//     * 这里case语句的意思是，如果访问日志中有访问记录，且访问记录的create_time在指定日期范围内，则为新访客，否则为老访客
//     * 然后这里可以看到加了<script>标签，不然是无法解析动态<foreach>标签的
//     */
//    @Select("<script> " +
//            "SELECT " +
//            "    user, " +
//            "    CASE " +
//            "        WHEN MIN(create_time) BETWEEN #{startDate} AND #{endDate} THEN '新访客' " +
//            "        ELSE '老访客' " +
//            "    END AS uvType " +
//            "FROM " +
//            "    t_link_access_logs " +
//            "WHERE " +
//            "    full_short_url = #{fullShortUrl} " +
//            "    AND gid = #{gid} " +
//            "    AND user IN " +
//            "    <foreach item='item' index='index' collection='userAccessLogsList' open='(' separator=',' close=')'> " +
//            "        #{item} " +
//            "    </foreach> " +
//            "GROUP BY " +
//            "    user;" +
//            "    </script>"
//    )
//    List<Map<String, Object>> selectUvTypeByUsers(
//            @Param("gid") String gid,
//            @Param("fullShortUrl") String fullShortUrl,
//            @Param("startDate") String startDate,
//            @Param("endDate") String endDate,
//            @Param("userAccessLogsList") List<String> userAccessLogsList
//    );
//
//    /**
//     * 根据分组获取指定日期内PV、UV、UIP数据
//     * 不根据full_short_url分组
//     */
//    @Select("SELECT " +
//            "    COUNT(user) AS pv, " +
//            "    COUNT(DISTINCT user) AS uv, " +
//            "    COUNT(DISTINCT ip) AS uip " +
//            "FROM " +
//            "    t_link_access_logs " +
//            "WHERE " +
//            "    gid = #{param.gid} " +
//            "    AND create_time BETWEEN #{param.startDate} and #{param.endDate} " +
//            "GROUP BY " +
//            "    gid;")
//    LinkAccessStatsDO findPvUvUidStatsByGroup(@Param("param") ShortLinkGroupStatsReqDTO requestParam);
//
//
//    /**
//     * 根据分组获取指定日期内高频访问IP数据
//     * 不根据full_short_url分组
//     */
//    @Select("SELECT " +
//            "    ip, " +
//            "    COUNT(ip) AS count " +
//            "FROM " +
//            "    t_link_access_logs " +
//            "WHERE " +
//            "    gid = #{param.gid} " +
//            "    AND create_time BETWEEN #{param.startDate} and #{param.endDate} " +
//            "GROUP BY " +
//            "    gid, ip " +
//            "ORDER BY " +
//            "    count DESC " +
//            "LIMIT 5;")
//    List<HashMap<String, Object>> listTopIpByGroup(@Param("param") ShortLinkGroupStatsReqDTO requestParam);
//
//
//    /**
//     * 获取分组用户信息是否新老访客
//     */
//    @Select("<script> " +
//            "SELECT " +
//            "    user, " +
//            "    CASE " +
//            "        WHEN MIN(create_time) BETWEEN #{startDate} AND #{endDate} THEN '新访客' " +
//            "        ELSE '老访客' " +
//            "    END AS uvType " +
//            "FROM " +
//            "    t_link_access_logs " +
//            "WHERE " +
//            "    gid = #{gid} " +
//            "    AND user IN " +
//            "    <foreach item='item' index='index' collection='userAccessLogsList' open='(' separator=',' close=')'> " +
//            "        #{item} " +
//            "    </foreach> " +
//            "GROUP BY " +
//            "    user;" +
//            "    </script>"
//    )
//    List<Map<String, Object>> selectGroupUvTypeByUsers(
//            @Param("gid") String gid,
//            @Param("startDate") String startDate,
//            @Param("endDate") String endDate,
//            @Param("userAccessLogsList") List<String> userAccessLogsList
//    );
}
